﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BookManage.Model;
using System.Data.SqlClient;
using System.Data;

namespace BookManage.DAL
{
    public class ReaderDAL
    {
       public static int Add(Reader reader)
        {
            int rows = 0;
            string sql = "insert into TB_Reader(rdID,rdName,rdSex,rdType,rdDept,rdPhone,rdEmail,rdDateReg,rdPhoto,rdStatus,rdBorrowQty,rdPwd,rdAdminRoles)"
                + " values(@rdID,@rdName,@rdSex,@rdType,@rdDept,@rdPhone,@rdEmail,@rdDateReg,@rdPhoto,@rdStatus,@rdBorrowQty,@rdPwd,@rdAdminRoles)";
            SqlParameter[] parameters =
            {
                new SqlParameter("@rdID",reader.rdID),
                new SqlParameter("@rdName",reader.rdName),
                new SqlParameter("@rdSex",reader.rdSex),
                new SqlParameter("@rdType",reader.rdType),
                new SqlParameter("@rdDept",reader.rdDept),
                new SqlParameter("@rdPhone",reader.rdPhone),
                new SqlParameter("@rdEmail",reader.rdEmail),
                new SqlParameter("@rdDateReg",reader.rdDateReg),
                new SqlParameter("@rdPhoto",reader.rdPhoto),
                new SqlParameter("@rdStatus",reader.rdStatus),
                new SqlParameter("@rdBorrowQty",reader.rdBorrowQty),
                new SqlParameter("@rdPwd",reader.rdPwd),
                new SqlParameter("@rdAdminRoles",reader.rdAdminRoles),
            };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        }

        public static DataTable GetAll(int rdID)
        {
            string sql;
            sql = "select * from TB_reader where rdID=@rdID";
            SqlParameter[] parameters =
                {
                    new SqlParameter("@rdID",rdID)
                };
            return SqlHelper.GetDataTable(sql, parameters, "TB_Reader");
        }

        public static DataTable GetReaderByRD(int rdID, string rdName)
        {
            string sql;
            if (rdID <= 0 && rdName == "")
            {
                sql = "select * from TB_Reader where 0=1";
                return SqlHelper.GetDataTable(sql, null, "TB_Reader");
            }
            rdName = (rdName == "") ? ("%") : ("%" + rdName + "%");
            if (rdID <= 0)
            {
                sql = "select rdID,rdName,rdAdminRoles from TB_Reader where rdName like @rdName";
                SqlParameter[] parameters =
                {
                    new SqlParameter("@rdName",rdName)
                };
                return SqlHelper.GetDataTable(sql, parameters, "TB_Reader");
            }
            else
            {
                sql = "select rdID,rdName,rdAdminRoles from TB_Reader where rdID=@rdID and rdName like @rdName";
                SqlParameter[] parameters =
                {
                    new SqlParameter("@rdID",rdID),
                    new SqlParameter("@rdName",rdName)
                };
                return SqlHelper.GetDataTable(sql, parameters, "TB_Reader");
            }
        }

        public static int IsFindReader(int rdID)
        {
            int rows = 0;
            string sql = "select count(*) from TB_Reader where rdID=@rdID";
            SqlParameter[] parameters = { new SqlParameter("@rdID", rdID) };
            try
            {
                rows = Convert.ToInt32(SqlHelper.ExecuteScalar(sql, parameters).ToString());
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        }

        public static DataTable GetLastRdID()
        {
            string sql = "select top 1 * from TB_Reader order by rdID desc";
            return SqlHelper.GetDataTable(sql, null, "TB_Reader");
        } //获取最后一个读者号

        public static int Update(Reader reader)
        {
            int rows = 0;
            string sql = "update TB_Reader set "
                + "rdID=@rdID,"
                + "rdName=@rdName,"
                + "rdSex=@rdSex,"
                + "rdType=@rdType,"
                + "rdDept=@rdDept,"
                + "rdPhone=@rdPhone,"
                + "rdEmail=@rdEmail,"
                + "rdDateReg=@rdDateReg,"
                + "rdPhoto=@rdPhoto,"
                + "rdStatus=@rdStatus,"
                + "rdBorrowQty=@rdBorrowQty,"
                + "rdPwd=@rdPwd,"
                + "rdAdminRoles=@rdAdminRoles "
                + "where rdID=@rdID";
            SqlParameter[] parameters =
            {
                new SqlParameter("@rdID",reader.rdID),
                new SqlParameter("@rdName",reader.rdName),
                new SqlParameter("@rdSex",reader.rdSex),
                new SqlParameter("@rdType",reader.rdType),
                new SqlParameter("@rdDept",reader.rdDept),
                new SqlParameter("@rdPhone",reader.rdPhone),
                new SqlParameter("@rdEmail",reader.rdEmail),
                new SqlParameter("@rdDateReg",reader.rdDateReg),
                new SqlParameter("@rdPhoto",reader.rdPhoto),
                new SqlParameter("@rdStatus",reader.rdStatus),
                new SqlParameter("@rdBorrowQty",reader.rdBorrowQty),
                new SqlParameter("@rdPwd",reader.rdPwd),
                new SqlParameter("@rdAdminRoles",reader.rdAdminRoles),
            };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        } //更新读者
        public static int Loss(Reader reader)
        {
            int rows = 0;
            string sql = "update TB_Reader set rdStatus='挂失' where rdID=@rdID";
            SqlParameter[] parameters = { new SqlParameter("@rdID", reader.rdID) };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        } //挂失
        public static int UnLoss(Reader reader)
        {
            int rows = 0;
            string sql = "update TB_Reader set rdStatus='有效' where rdID=@rdID";
            SqlParameter[] parameters = { new SqlParameter("@rdID", reader.rdID) };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        }  //解除挂失

        public static int Delete(Reader reader)
        {
            int rows = 0;
            string sql = "delete from TB_Reader where rdID=@rdID";
            SqlParameter[] parameters = { new SqlParameter("@rdID", reader.rdID) };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        } //删除
        public static Reader GetObjectByID(int rdID)
        {
            DataRow dr = GetDRByID(rdID);
            return SqlHelper.DataRowToT<Reader>(dr);
        }
        public static DataRow GetDRByID(int rdID)
        {
            string sql = "select * from TB_Reader where rdID=@rdID";
            SqlParameter[] parameters = { new SqlParameter("@rdID", rdID) };
            DataTable dt = null;
            dt = SqlHelper.GetDataTable(sql, parameters, "TB_Reader");
            DataRow dr = null;
            if (dt == null || dt.Rows.Count == 0)
                return dr;
            else
            {
                dr = dt.Rows[0];
                return dr;
            }
        }
        public static DataTable GetAllReader()
        {
            string sql = "select rdID,rdName,rdSex,rdType,rdDateReg,rdStatus,rdBorrowQty from TB_Reader where 0=0";
            return SqlHelper.GetDataTable(sql, null, "TB_Reader");
        }
        public static DataTable GetReader(int rdType, string rdDept, string rdName)
        {
            string sql;
            if (rdType <= 0 && rdDept == "" && rdName == "")
            {
                sql = "select * from TB_Reader where 0=1";
                return SqlHelper.GetDataTable(sql, null, "TB_Reader");
            }

            rdDept = (rdDept == "") ? ("%") : ("%" + rdDept + "%");
            rdName = (rdName == "") ? ("%") : ("%" + rdName + "%");
            if (rdType <= 0)
            {
                sql = "select rdID,rdName,rdSex,rdType,rdDateReg,rdStatus,rdBorrowQty from TB_Reader where rdDept like @rdDept and rdName like @rdName";
                SqlParameter[] parameters =
                {
                    new SqlParameter("@rdDept",rdDept),
                    new SqlParameter("@rdName",rdName)
                };
                return SqlHelper.GetDataTable(sql, parameters, "TB_Reader");
            }
            else
            {
                sql = "select rdID,rdName,rdSex,rdType,rdDateReg,rdStatus,rdBorrowQty from TB_Reader where rdType=@rdType and rdDept like @rdDept and rdName like @rdName";
                SqlParameter[] parameters =
                {
                    new SqlParameter("@rdType",rdType),
                    new SqlParameter("@rdDept",rdDept),
                    new SqlParameter("@rdName",rdName)
                };
                return SqlHelper.GetDataTable(sql, parameters, "TB_Reader");
            }
        }  //通过读者类别，读者专业，读者姓名获取读者信息
        public static int ChangePwd(Reader readerPwd)
        {
            int rows = 0;
            string sql = "update TB_Reader set rdPwd=@rdPwd where rdID=@rdID";
            SqlParameter[] parameters =
            {
                new SqlParameter("@rdID",readerPwd.rdID),
                new SqlParameter("@rdPwd",readerPwd.rdPwd)
            };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch(SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        } //修改密码
        /* public static DataTable ChangeRoles(int rdID, int rdAdminRoles)
         {
             string sql = "update TB_Reader set rdAdminRoles=@rdAdminRoles where rdID=@rdID";
             SqlParameter[] parameters =
             {
                 new SqlParameter("@rdID",rdID),
                 new SqlParameter("@rdAdminRoles",rdAdminRoles)
             };
             return SqlHelper.GetDataTable(sql, parameters, "TB_Reader");
         }*/
        public static int ChangeRoles(Reader reader)
        {
            int rows = 0;
            string sql = "update TB_Reader set rdAdminRoles=@rdAdminRoles where rdID=@rdID";
            SqlParameter[] parameters =
            {
                new SqlParameter("@rdID",reader.rdID),
                new SqlParameter("@rdAdminRoles",reader.rdAdminRoles)
            };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        } //修改用户权限

    }
}
